<?php

class Kieumau_Db_Common extends Zend_Db_Table_Abstract
{	

	// this name is table's name, it shouldn't set null in private table.
	protected $_name = '' ;
	
	// This array will contain all field of primary table. It can't empty
	protected $fieldsPrimary = array();
	
	// This will be used to update data for table_lang
	// if this empty, the system don't update data for table_lang
	protected $fieldsLanguage = array();
	
	public function insertData($table_name, $data){
		try{
			$this->_db->insert($table_name,$data);
			return $this->_db->lastInsertId(); 			
		}catch(Exception $ex){
			echo $ex->getMessage();
			return -1;
		}
	}
	
	 public function updateData($table_name, $data, $id){
    	try{
			if(isset($data['id']))
				unset($data['id']);
			$this->_db->update($table_name, $data, 'id = '. (int)$id );
			return $id; 			
    	}catch(Exception $ex){
    		return -1;
    	}
    }
	
	// @author: Peter.Nguyen
	// $date: 5/25/2012
	// @function: insertTable
	// @return: id row of table 
	 public function insertTable($data){
		 
		// Id of table pri
		$idTablePri = 0;
    	try{
			
			// Add data into primary table and return id primary of table
			$dataPrimary = array();
			foreach( $this->fieldsPrimary as $field ){
				if(isset($data[$field])){
					$dataPrimary[$field] = $data[$field];	
				}
			}
    		$this->_db->insert($this->_name, $dataPrimary);
			$idTablePri = $this->_db->lastInsertId();
			
			// Add data language into second table (table_lang)
			if(count($this->fieldsLanguage)&&$idTablePri>0){
				
				$language = new Kieumau_Db_Language();
				$objLanguages = $language->getLanguages();
				$objLanguagesDefault = $language->getLanguageDefault();
				
				foreach( $objLanguages as $objLanguage ){
					$dataLanguage = array();
					$dataLanguage[$this->_name."_id"] = $idTablePri;
					$dataLanguage["language_id"] = $objLanguage['code'];
					foreach( $this->fieldsLanguage as $field ){
						if(isset($data[$field][$objLanguage['code']])&&$data[$field][$objLanguage['code']]!=""){
							$dataLanguage[$field] = $data[$field][$objLanguage['code']];	
						}else{
							$dataLanguage[$field] = $data[$field][$objLanguagesDefault['code']];	
						}
					}
					$this->insertData($this->_name."_language", $dataLanguage);
				}
			}
			
			return $idTablePri; 	
					
    	}catch(Exception $ex){
    		return -1;
    	}
    }
	
	// @author: Peter.Nguyen
	// $date: 5/25/2012
	// @function: updateTable
	// @return: id row of table 
	 public function updateTable($data, $id){
	
    	try{
			if(isset($data['id']))
				unset($data['id']);

			// Add data into primary table and return id primary of table
			$dataPrimary = array();
			foreach( $this->fieldsPrimary as $field ){
				if(isset($data[$field])){
					$dataPrimary[$field] = $data[$field];	
				}
			}			
    		$this->_db->update($this->_name, $dataPrimary, 'id = '. (int)$id );
			$idTablePri = $id;
			
			// Add data language into second table (table_lang)
			if(count($this->fieldsLanguage)&&$idTablePri>0){
				$language = new Kieumau_Db_Language();
				$objLanguages = $language->getLanguages();
				$objLanguagesDefault = $language->getLanguageDefault();
				foreach( $objLanguages as $objLanguage ){
					$dataLanguage = array();
					$dataLanguage[$this->_name."_id"] = $idTablePri;
					$dataLanguage["language_id"] = $objLanguage['code'];
					foreach( $this->fieldsLanguage as $field ){
						if(isset($data[$field][$objLanguage['code']])){
							$dataLanguage[$field] = $data[$field][$objLanguage['code']];	
						}else{
							$dataLanguage[$field] = $data[$field][$objLanguagesDefault['code']];	
						}
					}
					$this->_db->update($this->_name."_lang", $dataLanguage, $this->_name."_id = ".$idTablePri." AND language_id = '".$objLanguage['code']."'");
					
				}
			}
			return $id; 			
    	}catch(Exception $ex){
    		return -1;
    	}
    }
	
	// @author: Peter.Nguyen
	// $date: 5/25/2012
	// @function: deleteTable
	// @return: id row of table 
	 public function delTable($id){
    	try{
			if(isset($data['id']))
				unset($data['id']);
			$this->_db->delete($this->_name."_lang",$this->_name.'_id = '. (int)$id );
			return $this->_db->delete($this->_name,'id = '. (int)$id ); 			
    	}catch(Exception $ex){
    		return -1;
    	}
    }
	
	// @author: Peter.Nguyen
	// $date: 5/29/2012
	// @function: delTables
	// @return: true or false
	 public function delTables($ids){
    	try{
			if(is_array($ids)){
				foreach($ids as $id){
					$this->delTable($id);	
				}
			}
			return true;
			
    	}catch(Exception $ex){
    		return -1;
    	}
    }
				
	// @author: Peter.Nguyen
	// $date: 5/25/2012
	// @function: updateTable
	// @return: id row of table 	
    public function getTable($id = 0){
    	try {
			if($id){
				$objPrimaryTable = $this->_db->fetchAll ( 'SELECT * FROM `'.$this->_name.'` WHERE `id`= ?' , array($id) );
				if(count($objPrimaryTable)<=0) return;
				
				$objPrimaryTable = $objPrimaryTable[0];
				if(count($this->fieldsLanguage)>0){
					$objLanguagesTable = $this->_db->fetchAll ( 'SELECT * FROM `'.$this->_name.'_lang` WHERE `group_id`= ?' , array($id) );
					foreach($objLanguagesTable as $objTable){
						foreach( $this->fieldsLanguage as $field ){
							$objPrimaryTable[$field][$objTable["language_id"]] = $objTable[$field];
						}	
					}
				}
				return $objPrimaryTable;
			}
			return;
			
    	} catch ( Exception $ex ) {
    		echo ("<br /> Can not get all information, error: " . $ex->getMessage ());
    		exit ();
    	}
    }
	
	// @author: Peter.Nguyen
	// $date: 5/25/2012
	// @descriptions: get multi row with condition where
	// @function: updateTable
	// @return: id row of table 	
    public function getTables($where = ""){
    	try {
			$sql = 'SELECT * FROM `'.$this->_name;
			if($where != ""){
				$sql .=	'` WHERE '.$where;
			}
			$arrPrimaryTable = $this->_db->fetchAll( $sql );
			foreach($arrPrimaryTable as $key=>$objPrimaryTable){
				if(count($this->fieldsLanguage)>0){
					$objLanguagesTable = $this->_db->fetchAll ( 'SELECT * FROM `'.$this->_name.'_lang` WHERE `group_id`= ?' , array($objPrimaryTable['id']) );
					foreach($objLanguagesTable as $objTable){
						foreach( $this->fieldsLanguage as $field ){
							$objPrimaryTable[$field][$objTable["language_id"]] = $objTable[$field];
						}	
					}
				}
				$arrPrimaryTable[$key] = $objPrimaryTable;
			}
			return $arrPrimaryTable;
			
    	} catch ( Exception $ex ) {
    		echo ("<br /> Can not get all information, error: " . $ex->getMessage ());
    		exit ();
    	}
    }
    
    // @author: Michael.Tran
    // $date: 07/24/2012
    // @descriptions: get multi row with condition where
    // @function: getAllByTable
    // @return: id row of table
    public function getAllByTable($where = ""){
    	$language = new Kieumau_Db_Language();
		$objLanguages = $language->getLanguages();
		//$objLanguagesDefault = $language->getLanguageDefault();
		
    	try {
    		$sql = 'SELECT * FROM `'.$this->_name.'`';
    		if($where != ""){
    			$sql .=	' WHERE '.$where;
    		}
    		
    		$sql .= ' order by level';
    		
    		$arrPrimaryTable = $this->_db->fetchAll( $sql );
    		
    		foreach($arrPrimaryTable as $key=>$objPrimaryTable){
    			if(count($this->fieldsLanguage)>0){
    				$objLanguagesTable = $this->_db->fetchAll ( 'SELECT * FROM `'.$this->_name.'_language` WHERE `'.$this->_name.'_id`= ? ' , array($objPrimaryTable['id']) );
    				foreach($objLanguagesTable as $objTable){
    					foreach( $this->fieldsLanguage as $field ){
    						foreach($objLanguages as $oneLanguage)
    							if($oneLanguage['id'] == $objTable["lang_id"]){
    								$langField = $oneLanguage['code'];
    							}
    						
    						$objPrimaryTable[$field][$langField] = $objTable[$field];
    					}
    				}
    			}
    			$arrPrimaryTable[$key] = $objPrimaryTable;
    		}
    		
    		return $arrPrimaryTable;
    	} catch ( Exception $ex ) {
    		echo ("<br /> Can not get all information, error: " . $ex->getMessage ());
    		exit ();
    	}
    }
    
    public function generateActivekey($password) {
    	try {
    		do {
    			$key = md5 ( time () );
    			$key .= md5 ( $password );
    			
    			
    			$sql = 'SELECT activekey FROM employee where activekey = "' . $key . '"';
    			$db = $this->_db;
    			$db->setFetchMode ( Zend_Db::FETCH_OBJ );
    			$activekey = $db->fetchAll ( $sql );
    		} while ( ! empty ( $activekey ) );
    		return $key;
    	} catch ( Exception $ex ) {
    		Echo ("<br /> generate Active Key Failed, error: " . $ex->getMessage ());
    		exit ();
    	}
    }
    
    public function getFid($table_name){
    	try {
	    	$sql = 'Select id from '.$table_name.' order by id desc limit 1';
	    	$db = $this->_db;
	    	$db->setFetchMode ( Zend_Db::FETCH_ASSOC );
	    	$fid = $db->fetchAll ( $sql );
	    	if(empty($fid))
	    		$fid[0]['id'] = 0;
    		$fid = (int)($fid[0]['id']) + 1;
	    	return $fid;
    	} catch ( Exception $ex ) {
    		Echo ("<br /> Can not get final id, error: " . $ex->getMessage ());
    		exit ();
    	}
    }
    
    public function getAll($table_name, $where="", $param = array()){
    	try {
			
    		$this->_db->setFetchMode(Zend_Db::FETCH_ASSOC);
    		$sql = (empty($where))? 'SELECT * FROM `'.$table_name.'`' : 'SELECT * FROM `'.$table_name.'` where '.$where;
			return $this->_db->fetchAll ( $sql, $param );
    	} catch ( Exception $ex ) {
    		echo "<br/>Can not get all information, Error: " . $ex->getMessage ();
			//echo "<br/> sql la: ".$sql;
    		exit ();
    	}
    }
    
    public function getColumn($table_name, $column_name = array(), $where="" ,$param = array()){
    	try {			
	    	$sql = 'Select ';
	    	$max = count($column_name) -1;
	    	for($i = 0; $i < $max ; ++$i)
	    		$sql .= $column_name[$i].', ';
	    	if(!empty($column_name[$max])) $sql .= $column_name[$max];
	    	$sql .=  ' from '.$table_name;
	    	if($where!= "")
				$sql .= ' where '.$where;
			
	    	$db = $this->_db;
	    	$db->setFetchMode ( Zend_Db::FETCH_ASSOC );
	    	$result = $db->fetchAll ( $sql, $param );
			
	    	return $result;
    	} catch ( Exception $ex ) {
    		Echo ("<br /> Can not get columns from table ".$table_name.", error: " . $ex->getMessage ());
    		echo "<br/> sql la: $sql";
    		exit ();
    	}
    }
    
    
    public function getAlbum($id )
    {
    	$id = (int)$id ;
    	
    	$row = $this->fetchRow( 'id = '  . $id );
    	
    	if  (!$row) {
    		throw new Exception("Could not find row $id ");
    	}
    	return $row->toArray();
    }
    
    public function  addAlbum($artist, $title )
    {
    	$data  = array(
    			'artist' =>  $artist,
    			'title'  =>  $title ,
    	);
    	$this->insert($data);
    }
    
    public function  updateAlbum($id , $artist, $title )
    {
    	$data  = array(
    			'artist' =>  $artist,
    			'title'  =>  $title ,
    	);
    	$this->update($data, 'id = '. (int)$id );
    }
    
    public function  deleteAlbum($id )
    {
    	$this->delete('id =' . (int)$id );
    }
    
	
	 public function dangnhap($email, $password){
    	try {
			//Dữ liệu injection bình thường: ' OR ''='
			$select = $db->select();
			$sql = "Select * from employer where email = ? and password = ?";
    		$db = $this->_db;
    		$db->setFetchMode ( Zend_Db::FETCH_ASSOC );
    		$result = $db->fetchAll($sql,array($email, $password) );			
    		return $result;
    	} catch ( Exception $ex ) {
    		echo "<br/>Can not validate activekey, Error: " . $ex->getMessage ();
    	}
    }//Viết hàm này để thử nghiệm lỗi injection cho đăng nhập. Kết quả: chặn thành công, ko bị injection
	
	

 	public function deleteContent($tableName, $where )
    {
    	try{
			$this->_db->delete($tableName,$where);
		}catch(Exception $ex){
			echo "Error: ".$ex->getMessage();
			return false;
		}
    }
    
    
    // @author: Michael.Tran
    // $date: 07/25/2012
    // @descriptions: get multi row with condition where
    // @function: changeDefaultLanguage
    public function changeDefaultLanguage($newDefaultLanguageCode){
    	try {
    		$resetValue = array('default'=>'0');
    		$defaultValue = array('default'=>'1');
    		//Reset all default language default to 0;
    		$this->_db->update($this->_name, $resetValue);
    		//Set new default language
    		$this->_db->update($this->_name, $defaultValue, ' code = "'.$newDefaultLanguageCode.'"');
    	} catch ( Exception $ex ) {
    		echo $ex->getMessage ();
    	}
    }
    
    // @author: Michael.Tran
    // $date: 07/25/2012
    // @descriptions: get multi row with condition where
    // @function: changeDefaultLanguage
    public function deleteTableLanguage($id){
    	try{
			//Delete from language table
			$this->_db->delete($this->_name."_language",$this->_name.'_id = '. (int)$id );
			//Delete from main table
			return $this->_db->delete($this->_name,'id = '. (int)$id ); 			
    	}catch(Exception $ex){
    		return -1;
    	}
    }
    
}